package com.daiyilu.com.office4all.excel.write;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.JSON;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class Export4Column {
    public static void main(String[] args) throws FileNotFoundException {
        test04();
    }


    public static void test04() throws FileNotFoundException {
        List<?> data = data();
//        EasyExcel.write(new FileOutputStream("D:\\home\\excel\\template.xlsm")).sheet().doWrite(data);
        EasyExcel.write(new FileOutputStream("D:\\home\\excel\\template11.xlsm"))
                .withTemplate("D:\\home\\excel\\template.xlsm")
                .registerWriteHandler(new ComplexHandler())
                .sheet()
                .doWrite(data);
    }

    /***
     * 设置 excel 的样式
     * @return
     */
    private static WriteHandler createTableStyle() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PINK.getIndex());
        // 设置字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 20);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());

        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 20);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 设置边框的样式
        contentWriteCellStyle.setBorderBottom(BorderStyle.DASHED);
        contentWriteCellStyle.setBorderLeft(BorderStyle.DASHED);
        contentWriteCellStyle.setBorderRight(BorderStyle.DASHED);
        contentWriteCellStyle.setBorderTop(BorderStyle.DASHED);

        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }

    /*
        easyexcel无模板方式封装数据 传递的参数实际泛型为一个map
    */
    protected static List<?> data() {

        //获取基础数据
        List<Object> templateDatas = getTemplateDatas();
        List<Map<String, Object>> rowDbDatas = getRealDatas();

        List<List<Object>> showDatas = new ArrayList<>();

        int num = 5;

        final List<List<Map<String, Object>>> alldatas = splitList(rowDbDatas, num);

        for (int i = 0; i < alldatas.size(); i++) {
            for (int j = 0; j < templateDatas.size(); j++) {
                Map<Integer, Object> o = (Map<Integer, Object>) templateDatas.get(j);
                List<Object> rowDatas = new ArrayList<>();
                rowDatas.add(o.get(0));
                for (int k = 0; k < alldatas.get(i).size(); k++) {
                    Map<String, Object> rowData = alldatas.get(i).get(k);
                    //强转为map键为列索引
                    String os = ((String) o.get(1)).replace("{", "").replace("}", "").replace(".", "");
                    rowDatas.add(rowData.get(os));

                }
                showDatas.add(rowDatas);
            }
            showDatas.add(new ArrayList<>());
        }


        return showDatas;
    }

    private static List<Map<String, Object>> getRealDatas() {
        String a = "{\n" +
                "\t\"xuhao\": 2,\n" +
                "\t\"pic\": \"https://img.alicdn.com/bao/uploaded/i2/3940925619/O1CN01IrV5uF1rNYBuhQ4o6_!!0-item_pic.jpg\",\n" +
                "\t\"dianpu\": \"雪梨店铺\",\n" +
                "\t\"clazz\": \"分类店铺123\",\n" +
                "\t\"goods_id\": \"658205193516\",\n" +
                "\t\"name\": \"商品名称111\",\n" +
                "\t\"updatedDate\": \"2021-10-27\",\n" +
                "\t\"salesBy10\": 10.0000,\n" +
                "\t\"salesBy30\": 30.0000,\n" +
                "\t\"totalMoney\": 100.0000,\n" +
                "\t\"other\": \"其他备注\"\n" +
                "}";
        String b = "{\n" +
                "\t\"xuhao\": 2,\n" +
                "\t\"pic\": \"https://img.alicdn.com/bao/uploaded/i1/1705086826/O1CN0162WSw720IMG4v3tg6_!!1705086826.jpg\",\n" +
                "\t\"dianpu\": \"林珊珊\",\n" +
                "\t\"clazz\": \"分类店铺123\",\n" +
                "\t\"goods_id\": \"658205193516\",\n" +
                "\t\"name\": \"商品名称111\",\n" +
                "\t\"updatedDate\": \"2021-10-27\",\n" +
                "\t\"salesBy10\": 10.0000,\n" +
                "\t\"salesBy30\": 30.0000,\n" +
                "\t\"totalMoney\": 100.0000,\n" +
                "\t\"other\": \"其他备注\"\n" +
                "}";

        Map<String, Object> amap = (Map<String, Object>) JSON.parse(a);
        Map<String, Object> bmap = (Map<String, Object>) JSON.parse(b);

//        List<Object> values = Arrays.asList(test.values().toArray());

        List<Map<String, Object>> rowList = new ArrayList<>();

        for (int i = 0; i < 101; i++) {
            rowList.add(amap);
            rowList.add(amap);
            rowList.add(amap);
            rowList.add(bmap);
        }
        return rowList;
    }

    private static List<Object> getTemplateDatas() {
        //获取excelReaderBuilder
        ExcelReaderBuilder read = null;
        try {
            read = EasyExcel.read(new FileInputStream("D:\\home\\excel\\column.xlsx"));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        ExcelReaderSheetBuilder sheetBuilder = read.sheet(0);



        ExcelReaderSheetBuilder sheet1 = sheetBuilder.headRowNumber(0);
        //获取excel中所有的数据
        List<Object> list = sheet1.doReadSync();


        return list;
    }

    public static List<List<Map<String, Object>>> splitList(List<Map<String, Object>> list, int len) {
        if (list == null || list.size() == 0 || len < 1) {
            return null;
        }

        List<List<Map<String, Object>>> result = new ArrayList<>();
        int size = list.size();
        int count = (size + len - 1) / len;
        for (int i = 0; i < count; i++) {
            List<Map<String, Object>> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
            result.add(subList);
        }
        return result;
    }
}


